<?php
/*
 * Contains all functions needed to communicate with the database. Actually, it invokes the respective stored procedures per function
 * It will utilize several classes from the model. Invoked by controllers exclusively.
 */
class DatabaseManager {
	
	protected $mysqliObj;
	protected $url;
	protected $dbname;
	protected $username;
	protected $password;
	
	function __construct($url, $dbname, $username, $password) {
		$this->url = $url;
		$this->dbname = $dbname;
		$this->username = $username;
		$this->password = $password;
	}
	
	function connect() {
		$this->mysqliObj = new mysqli($this->url, $this->username, $this->password, $this->dbname);
		if ($this->mysqliObj->connect_errno) {
			echo "Failed to connect to MySQL: (" . $this->mysqliObj->connect_errno . ") " . $this->mysqliObj->connect_error;
			return false;
		}
		return true;
	}
	
	function storeOrder($order) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				die("Could not establish connection with the database");
			}
		}
		$results = $this->mysqliObj->query("CALL checkout_order('".$order."', @result);");
		$result = $this->mysqliObj->query("SELECT @result;");
		
		$row = $result->fetch_row();
		return $row[0] == '1';
		
		
	}

	function addMember($fname, $lname, $uname, $password, $ctype, $cnumber, $address, $manager, $pnumber){
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				return false;
			}
		}
		$results = $this->mysqliObj->query("CALL new_member('".$fname."', '".$lname."', '".$uname."', '".$password."', '".$ctype."', '".$cnumber."', '".$address."', '".$pnumber."', false, @result)");
		//echo "CALL new_member('".$fname."', '".$lname."', '".$uname."', '".$password."', '".$ctype."', '".$cnumber."', '".$address."', '".$pnumber."', false, @result)";
		$result = $this->mysqliObj->query("SELECT @result;");
		
		$row = $result->fetch_row();
		return $row[0] == '1';
		
	}
	
	function addMovie($title, $year, $directorID, $rating, $noCopies, $price, $cat, $actors) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				return false;
			}
		}
		$results = $this->mysqliObj->query("CALL new_movie('".$title."', '".$year."', '".$directorID."', '".$rating."', '".$noCopies."', '".$price."', '".$cat."', '".$actors."', @result)");
		$result = $this->mysqliObj->query("SELECT @result;");
		
		$row = $result->fetch_row();
		return $row[0] == '1';
	}
	
	function getDirectors() {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				return null;
			}
		}
		$results = $this->mysqliObj->query("CALL fetch_directors()");
		return $results;
	}
	
	function updateCopies($m, $c) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				return null;
			}
		}
		$results = $this->mysqliObj->query("CALL update_copies('".$m."', '".$c."', @result)");
		$result = $this->mysqliObj->query("SELECT @result;");
		
		$row = $result->fetch_row();
		return $row[0] == '1';
	}
	
	function getMovies($p, $l) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				return null;
			}
		}
		$results = $this->mysqliObj->query("CALL fetch_movies('".$p."', '".$l."')");
		return $results;
	}
	
	function getMostPopularMovies($m) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				return null;
			}
		}
		$results = $this->mysqliObj->query("CALL fetch_most_popular_movies('".$m."')");
		return $results;
	}
	
	function getMostPopularDirectors($m) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				return null;
			}
		}
		$results = $this->mysqliObj->query("CALL fetch_most_popular_directors('".$m."')");
		return $results;
	}
	
	function getMostPopularActors($m) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				return null;
			}
		}
		$results = $this->mysqliObj->query("CALL fetch_most_popular_actors('".$m."')");
		return $results;
	}
	
	function getAssociatedActors($id) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				return null;
			}
		}
		
		$results = $this->mysqliObj->query("CALL fetch_associated_actors('".$id."')");
		return $results;
	}
	
	function getActors() {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				return null;
			}
		}
		
		$results = $this->mysqliObj->query("CALL fetch_actors();");
		
		return $results;
	}
	
	function getBestClients($m) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				return null;
			}
		}
		
		$results = $this->mysqliObj->query("CALL fetch_best_clients('".$m."')");

		return $results;
	}
	
	function getSuggestedMovies($m) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				return null;
			}
		}
		
		$results = $this->mysqliObj->query("CALL get_buying_suggestions('".$m."')");
		
		return $results;
	}
	
	function validateMember($u, $p) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				die("Could not establish connection with the database");
			}
		}
		
		$results = $this->mysqliObj->query("CALL validate_user('".$u."', '".$p."')");
		$result = $this->mysqliObj->query("SELECT @result;");
		
		return $results;
	}
	
	function getMember($u) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				die("Could not establish connection with the database");
			}
		}
		
		$results = $this->mysqliObj->query("CALL get_user('".$u."')");
		
		return $results;
	}
	
	function getHistorySales($id) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				die("Could not establish connection with the database");
			}
		}
		$results = $this->mysqliObj->query("CALL get_history_sale('".$id."')");
		
		return $results;
	}
	
	function searchMovies($category, $title, $director) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				die("Could not establish connection with the database");
			}
		}
		
		$results = $this->mysqliObj->query("CALL search_movies('".$category."', '".$title."', '".$director."')");
		
		return $results;
	}
	
	function getCategories() {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				die("Could not establish connection with the database");
			}
		}
		
		$results = $this->mysqliObj->query("CALL fetch_categories()");
		
		return $results;
	}
	
	function getMovie($id) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				die("Could not establish connection with the database");
			}
		}
		
		$results = $this->mysqliObj->query("CALL get_movie_by_id('".$id."')");
		
		return $results;
	}
	
	function getEnhancedMovies($actor, $director, $title, $rating, $ar, $dr, $tr, $orderBy) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				die("Could not establish connection with the database");
			}
		}
		
		if (!isset($ar) || $ar == '') {
			$ar = 'AND';
		}
		if (!isset($dr) || $dr == '') {
			$dr = 'AND';
		}
		if (!isset($tr) || $tr == '') {
			$tr = 'AND';
		}
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				die("Could not establish connection with the database");
			}
		}
		$results = $this->mysqliObj->query("CALL search_movies_advanced('".$actor."', '".$director."', '".$title."', '".$rating."', '".$ar."', '".$dr."', '".$tr."', '".$orderBy."')");
		
		return $results;
	}
	
	function getMovieActors($mId) {
		if (!isset($mysqliObj)) {
			if (!$this->connect()) {
				die("Could not establish connection with the database");
			}
		}
		
		$results = $this->mysqliObj->query("CALL get_movie_actors('".$mId."')");
		
		return $results;
		
	}
	
	
	
	
}

?>